The invention relates to computer systems, and more particularly, to a method and mechanism for partitioning in a database system. Partitioning in a database system generally refers to the process of decomposing an object into a greater number of relatively smaller objects. Smaller objects are often easier to manage and more efficient to search than larger objects. Thus, database systems utilize partitioning to decompose objects such as tables and indexes into smaller and more manageable pieces or “partitions.”
Referring to FIG. 1a, a Sales Table 100 is shown containing a sample set of sales figures for a company. Each row contains a distinctive sales_id value (column 122) to uniquely reference a sale, as well as a store_id value (column 124) containing the system identification number of the store in which the sale was made, a product_id value (column 126) containing the system identification number of the product sold, a sales amount for the sale (column 128), and a time_id value (column 130) containing the system identification number for the date of the sale. Consider if it is desired to maintain this table as three separate partitions based upon the value in the “sales_amount” column 128. In particular, assume it is desired to form a first partition for all sales data corresponding to sales amount between $1 and $100, a second partition corresponding to all sales amounts between $101 and $1000, and a third partition corresponding to all sales amounts between $1001 and $2000.
The following Structured Query Language (“SQL”) statement can be used to create this partitioned table as three separate partitions (using the Oracle 8i database system available from Oracle Corporation of Redwood Shores, Calif.):
CREATE TABLE Sales (sale_idINTEGER,store_idINTEGER,product_idINTEGER,sales_amountNUMBER(10),time_idINTEGER )PARTITION BY RANGE ( sales_amount ) (PARTITION p0 VALUES LESS THAN (101) TABLESPACE TS0, PARTITION p1 VALUES LESS THAN (1001) TABLESPACE TS1, PARTITION p2 VALUES LESS THAN (2001) TABLESPACE TS2 );
FIG. 1b shows the three resulting partitions P0, P1, and P2 for the information logically contained in Sales Table 100. Partition P0 contains all rows in Sales Table 100 which has a sales_amount value less than $101 (rows 102, 106, and 110), partition P1 contains all rows having a sales_amount value from $101 to $1000 (rows 104 and 112), and partition P2 contains all rows having a sales_amount value from $1001 to $2000 (row 108).
Several advantages exist once a data object has been partitioned. As just one example, “partition pruning” can be used to more efficiently execute a query upon a partitioned table. If a database query is issued against a table and the query contains a search term that is limited by a certain range or value corresponding to a partitioning criteria, then only partitions having the desired range or value are searched; all other partitions outside the desired range are “pruned” from the search. If it is desired to search for all entries in the Sales Table 100 that have a sales amount between $1-50, then only partition P0 needs to be searched to satisfy the query. Partitions P1 and P2 contain entries that are clearly outside of the desired range; thus, they can be pruned from the search. Compared to non-partitioned tables, this type of partition pruning significantly reduces the volume of data that must be searched, thereby decreasing the response time and reducing the expense/overhead of responding to the query.
The present invention includes a method and mechanism for partitioning using information not directly located in the object being partitioned. According to an embodiment of the invention, foreign key-primary key relationships are utilized to create join conditions between multiple database tables to implement partitioning of a database object. Also disclosed are methods and mechanisms to perform partition pruning.
Further details of aspects, objects, and advantages of the invention are described below in the detailed description, drawings, and claims.